'''
Created on Sep 2, 2012

@author: kingsfield
'''
import csv
from util import SQL_CONF, Util
from collections import defaultdict
import MySQLdb as mdb
import util

wd = "/home/kingsfield/Desktop/BestBuyPc/"

if __name__ == '__main__':
    con = None
    con = mdb.connect(SQL_CONF.LOCATION, SQL_CONF.USER, SQL_CONF.PASSWD, SQL_CONF.DATABASE)
    cur = con.cursor()
    cur.execute("SELECT VERSION()")
    data = cur.fetchone()
    print "Init database done, version : %s " % data
    
    with open(wd + "train.csv") as infile:
        reader = csv.reader(infile, delimiter=",")
        reader.next() # burn the header

        cat_item_dict = defaultdict(lambda: defaultdict(int))
        idx = 0
        sum_i = 42366
        step = sum_i / 100
        for (user, sku, category, query, click_time, query_time, old_query) in reader:
            idx += 1
            if (idx % step) == 0:
                print '--%d' % (idx / step)
                con.commit()
#            words = Util.wordutil.getwords(query)
            query = query.replace('\'', ' ')
            query = query.strip()
            query = query.lower()
#            for word in words:
#                sql = 'insert into word (idx,uid,sku,cat,query,word,click_t,query_t) value (%d,\'%s\',\'%s\',\'%s\',\'%s\',\'%s\',\'%s\',\'%s\')' % (idx, user, sku, category, query, word, click_time, query_time)
##                print 'sql=%s' % sql 
#                cur.execute(sql)
            sql = 'insert into train (id,uid,sku,cat,query,click_t,query_t) value (%d,\'%s\',\'%s\',\'%s\',\'%s\',\'%s\',\'%s\')' % (idx, user, sku, category, query, click_time, query_time)
            #print sql
            cur.execute(sql)
        con.commit()
    print 'done'
